/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ebay.db;
import ebay.obj.*;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author Administrator
 */
public class DBcommon {
final static String DB="uml";
	final static String ID="root";
	final static String PW="";

	final static String CONNECT="jdbc:mysql://localhost:3306/"+DB+"?useUnicode=true&characterEncoding=utf8";

	static Connection aConnection;
	static Statement aStatement;


	public static void open()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver");
			aConnection = DriverManager.getConnection(CONNECT,ID,PW);
			aStatement= aConnection.createStatement();
		} catch (Exception e) {

			// TODO: handle exception
			System.out.println(e);
			System.exit(0);
		}
	}

	public static void close()
	{
		try {
			aStatement.close();
			aConnection.close();
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		}
	}
   private int DBConnectionString;

   /**
    * @roseuid 4DEC63580242
    */
   public DBcommon()
   {

   }

   /**
    * 登陆时检验用户是否合法
    * @param userID
    * @param password
    * @return Boolean
    * @roseuid 4DEC62970280
    */
   public Boolean checkCustomer(String userID, String password)
   {
      boolean flag = false;
		try {
			open();
			String sql="select * from customer";
			ResultSet result=aStatement.executeQuery(sql);
			while(result.next()){
				if(result.getString(2).compareTo(userID)==0&&
                        result.getString(3).compareTo(password)==0)
				{
					flag = true;
					break;
				}
			}
			close();
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		}
		return flag;
	}

   public void insertCustomer(Customer aCustomer)
   {
       try{
           open();
         String sql="insert into customer values (";
         sql=sql+aCustomer.getUserNo()+",";
         sql=sql+"'"+aCustomer.getUserID()+"',";
         sql=sql+"'"+aCustomer.getPassword()+"',";
         sql=sql+"'"+aCustomer.getName()+"',";
         sql=sql+"'"+aCustomer.getAdress()+"')";
		 aStatement.executeUpdate(sql);
         close();
          }
        catch (Exception e)
          {
	       // TODO: handle exception
		   System.out.println(e);
		  }
   }

   public void updateCustomer(Customer aCustomer)
   {
        try {
			open();
			String sql="update customer set ";
            sql=sql+"UserID='"+aCustomer.getUserID()+"'";
            sql=sql+"password='"+aCustomer.getPassword()+"'";
            sql=sql+"name='"+aCustomer.getName()+"'";
            sql=sql+"address='"+aCustomer.getAdress()+"'";

			aStatement.executeUpdate(sql);
			close();
	    	}
        catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		}
   }

   public int getUserNo(String userID)
   {   int number=0;
         try{
             open();
             String sql="select userNo " +
               "from customer where userID="+"'"+userID+"'";
             ResultSet result=aStatement.executeQuery(sql);
             result.next();
             number=result.getInt(1);
             close();
             }
         catch (Exception e)
             {
			 // TODO: handle exception
			 System.out.println(e);
		     }
       return number;
   }
   /**
    * 返回当前用户watch的商品的number数组
    * @param userNo
    * @return String[]
    * @roseuid 4DEC638D001F
    */
   public ArrayList listWatch(int userNo)
   {
      ArrayList<Integer> list = new ArrayList<Integer>();
      try{open();
          String sql="select number from watch where userNo="+userNo;
          ResultSet result=aStatement.executeQuery(sql);
          while(result.next())
          {
           list.add(result.getInt(1));
          }
          close();
         }
      catch (Exception e)
         {
	       // TODO: handle exception
		   System.out.println(e);
		  }
      return list;
   }

   /**
    * @param number - 商品的number
    * @roseuid 4DEC655701F4
    */
   public void addWatch(int number,int userNo)
   {
       try{
           open();
          String sql="insert into watch values (";
         sql=sql+number+",";
         sql=sql+userNo+")";
		 aStatement.executeUpdate(sql);
           close();
          }
        catch (Exception e)
          {
	       // TODO: handle exception
		   System.out.println(e);
		  }
   }

   /**
    * @param number - 商品的number
    * @roseuid 4DEC658A0167
    */
   public void deleteWatch(int number,int userNo)
   {
      try {
			open();
			String sql="delete from watch where number="+number+
                        " and userno="+userNo;
			aStatement.executeUpdate(sql);
			close();
		  }
         catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		 }
   }

   /**
    * 用number查询一个商品
    * @param number
    * @return AuctionItem
    * @roseuid 4DEC664B0177
    */

   public ArrayList<AuctionItem> listAuctionItem()
   {
       ArrayList<AuctionItem> list=new  ArrayList<AuctionItem>();
       try {
			open();
			String sql="select * from item";
			ResultSet result=aStatement.executeQuery(sql);
            AuctionItem auctionitem;
            while(result.next())
            {auctionitem=new AuctionItem();
             auctionitem.setNumber(result.getInt(1));
             auctionitem.setDescription(result.getString(2));
             auctionitem.setClosingDate(result.getTimestamp(3));
             auctionitem.setReservePrice(result.getInt(4));
             auctionitem.setHandlingCost(result.getInt(5));
             auctionitem.setShippingCost(result.getInt(6));
             auctionitem.setCurrentMaxPrice(result.getInt(7));
             auctionitem.setFixedPrice(result.getInt(8));
             auctionitem.setName(result.getString(9));
             auctionitem.setOwner(result.getInt(10));
             auctionitem.setBidder(result.getInt(11));
             auctionitem.setTime(result.getTimestamp(12));
             auctionitem.setState(result.getBoolean(13));
            }
			close();
		  }
         catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		 }
       return null;
   }

   public AuctionItem queryAuctionItem(int number)
   {
     AuctionItem auctionitem=new AuctionItem();
     try {
			open();
			String sql="select * from item where Number="+number;
			ResultSet result=aStatement.executeQuery(sql);
            result.next();
            auctionitem.setNumber(number);
            auctionitem.setDescription(result.getString(2));
            auctionitem.setClosingDate(result.getTimestamp(3));
            auctionitem.setReservePrice(result.getInt(4));
            auctionitem.setHandlingCost(result.getInt(5));
            auctionitem.setShippingCost(result.getInt(6));
            auctionitem.setCurrentMaxPrice(result.getInt(7));
            auctionitem.setFixedPrice(result.getInt(8));
            auctionitem.setName(result.getString(9));
            auctionitem.setOwner(result.getInt(10));
            auctionitem.setBidder(result.getInt(11));
            auctionitem.setTime(result.getTimestamp(12));
            auctionitem.setState(result.getBoolean(13));
			close();
		  }
         catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		 }
    return auctionitem;
   }
/**
 * 增加一个拍卖
 * @raram aBid
 */


  public void addBid(Bid aBid)
  {
   try {
			open();
			String sql="insert into Bid values (";
            sql=sql+aBid.getUserNo()+",";
			sql=sql+aBid.getNumber()+",";
            sql=sql+"'"+aBid.getPaymentMethod()+"',";
            sql=sql+"'"+aBid.getCurrency()+"',";
            sql=sql+"'"+aBid.getDate()+"',";
			sql=sql+aBid.getPrice()+")";
            //System.out.println(sql);
			aStatement.executeUpdate(sql);
			close();
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		}

  }
   /**
    * 上架一个商品
    * @param aAuctionItem
    * @roseuid 4DEC66C300BB
    */
   public void addAuctionItem(AuctionItem aAuctionItem)
   {
      try {
			open();
			String sql="insert into item values (";
            sql=sql+aAuctionItem.getNumber()+",";
			sql=sql+"'"+aAuctionItem.getDescription()+"',";
			sql=sql+"'"+aAuctionItem.getClosingDate()+"',";
			sql=sql+aAuctionItem.getReservePrice()+",";
            sql=sql+aAuctionItem.getHandlingCost()+",";
            sql=sql+aAuctionItem.getShippingCost()+",";
            sql=sql+aAuctionItem.getCurrentMaxPrice()+",";
            sql=sql+aAuctionItem.getFixedPrice()+",";
            sql=sql+"'"+aAuctionItem.getName()+"',";
            sql=sql+aAuctionItem.getOwner()+",";
            sql=sql+aAuctionItem.getBidder()+",";
            sql=sql+"'"+aAuctionItem.getTime()+"',";
			sql=sql+aAuctionItem.isState()+")";
            System.out.println(sql);
			aStatement.executeUpdate(sql);
			close();
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		}
   }

   /**
    * 更新最高价格
    * @param number
    * @param price
    * @roseuid 4DEC6711030D
 c   */

   public void changeAuctionState(int number)
   {
        try {
			open();
			String sql="update item set state=0 " +
                    "where number="+number;

			aStatement.executeUpdate(sql);
			close();
		    }
        catch (Exception e)
           {
			// TODO: handle exception
			System.out.println(e);
		}
   }


   public void updateMaxPrice(int number, int price,int userNo)
   {    AuctionItem auctionItem=new AuctionItem();
        try {
			open();
            if(price<=auctionItem.getCurrentMaxPrice())
            {System.out.println("Your price is too cheaper");
             return;
            }
			String sql="update item set ";
			sql=sql+"CurrentMaxPrice="+price;
            sql=sql+",bidder="+userNo;
			sql=sql+" where number="+number;

			aStatement.executeUpdate(sql);
			close();
	    	}
        catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		}
   }
}
